/**
	AUTHOR      : S.SUJAN KUMAR
	DATE		:  18 JULY 2005
	DESCRIPTION	: THIS FUNCTION RETURNS THE PROCESS FLOWS FOR THE GIVEN ProcessStep_ID 
			AND THE BACKWARD/FORWARD FLOWS FROM THE PROCESSSTEP_ID BASED ON THE @IS_BACKWARD_FLOW PARAMETER.
			@IS_BACKWARD_FLOW = 0 --BACKWARD FLOW
			@IS_BACKWARD_FLOW = 1 --FORWARD FLOW
*/
--SELECT * FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (27,0) ORDER BY PATH_NUMBER, Row_Id 
ALTER FUNCTION FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID ( @ProcessStep_ID INT, @IS_BACKWARD_FLOW INT )
 RETURNS @PATHS_TABLE TABLE( Row_ID INT IDENTITY, PATH_NUMBER INT,PROCESSSTEP_ID INT, PS_Type VARCHAR (50), PS_Type_Name VARCHAR(50), Product_Name VARCHAR (50), Product_Id INT )
AS
BEGIN
	DECLARE @CURRENT_PATH_NUMBER	INT
	DECLARE @NO_OF_PATHS		INT
	DECLARE @ROOT_NODE		INT
	DECLARE @CURRENT_NODE		INT
	DECLARE	@CHILD_NODES		TABLE ( Row_ID INT IDENTITY, ProcessStep_ID INT )
	DECLARE @PATH_NUMBER_STACK	TABLE ( Row_ID INT IDENTITY, Path_Number INT )
	DECLARE @TEMP_CHILD_NODES	TABLE ( Row_ID INT IDENTITY, ProcessStep_ID INT )
	DECLARE @Row_ID			INT
	DECLARE @CHILD_NODE		INT
	DECLARE @TEMP_COUNT		INT
	DECLARE @NEXT_PATH_NUMBER	INT
	DECLARE @PATH_CURSOR		CURSOR
	DECLARE @TEMP_ROW_ID INT
	
	SET @CURRENT_PATH_NUMBER	= 1
	SET @NO_OF_PATHS		= 1
	SET @ROOT_NODE			= @ProcessStep_ID

	INSERT INTO @CHILD_NODES( ProcessStep_ID ) VALUES ( @ProcessStep_ID )
	INSERT INTO @PATH_NUMBER_STACK ( Path_Number ) VALUES ( @CURRENT_PATH_NUMBER )
	INSERT INTO @PATHS_TABLE ( PATH_NUMBER, PROCESSSTEP_ID ) VALUES ( @CURRENT_PATH_NUMBER, @ProcessStep_ID )

	
	SELECT @Row_ID = Row_ID, @CURRENT_NODE = ProcessStep_ID FROM @CHILD_NODES WHERE Row_Id =  ( SELECT MAX( Row_Id ) FROM @CHILD_NODES )
	
	WHILE ( @Row_ID IS NOT NULL AND @Row_ID > 0 )
	 BEGIN
		DELETE FROM @CHILD_NODES WHERE Row_ID = @Row_ID
		DELETE FROM @TEMP_CHILD_NODES
		IF ( @IS_BACKWARD_FLOW = 0 )
		 BEGIN
			INSERT INTO @TEMP_CHILD_NODES SELECT ProcessStep_ID FROM Process_Step_Traverse_Table WHERE Next_ProcessStep_ID = @CURRENT_NODE
		 END
		ELSE
		 BEGIN
			INSERT INTO @TEMP_CHILD_NODES SELECT Next_ProcessStep_ID FROM Process_Step_Traverse_Table WHERE ProcessStep_ID = @CURRENT_NODE
		 END
		SET @TEMP_COUNT	= 0
		SELECT @TEMP_COUNT = COUNT(*) FROM @TEMP_CHILD_NODES
		IF ( @TEMP_COUNT > 1 )
		 BEGIN
			DECLARE @PATH_COUNT INT
			DECLARE @TEMP_PROCESSSTEP_ID INT
			DECLARE @TEMP_NO_OF_PATHS INT
			SET @TEMP_NO_OF_PATHS 	= @NO_OF_PATHS
			SET @PATH_COUNT	= 1
			WHILE ( @PATH_COUNT < @TEMP_COUNT )
			 BEGIN
				SET @TEMP_NO_OF_PATHS = @TEMP_NO_OF_PATHS + 1
				SET @PATH_CURSOR = CURSOR FOR SELECT PROCESSSTEP_ID  FROM @PATHS_TABLE WHERE PATH_NUMBER = @CURRENT_PATH_NUMBER
				OPEN @PATH_CURSOR 
				FETCH NEXT FROM @PATH_CURSOR INTO @TEMP_PROCESSSTEP_ID
				WHILE ( @@FETCH_STATUS = 0 )
				 BEGIN
					INSERT INTO @PATHS_TABLE( PATH_NUMBER, PROCESSSTEP_ID ) VALUES ( @TEMP_NO_OF_PATHS, @TEMP_PROCESSSTEP_ID )
					FETCH NEXT FROM @PATH_CURSOR INTO @TEMP_PROCESSSTEP_ID
				 END
				SET @PATH_COUNT	= @PATH_COUNT + 1
			 END
			
			SELECT @TEMP_ROW_ID 	= NULL
			SELECT @CHILD_NODE = ProcessStep_ID, @TEMP_ROW_ID = Row_Id FROM @TEMP_CHILD_NODES WHERE Row_Id =  ( SELECT MAX ( Row_Id ) FROM @TEMP_CHILD_NODES )
			DELETE FROM @TEMP_CHILD_NODES WHERE Row_id = @TEMP_ROW_ID
			INSERT INTO @PATHS_TABLE( PATH_NUMBER, PROCESSSTEP_ID ) VALUES ( @CURRENT_PATH_NUMBER, @CHILD_NODE )
			INSERT INTO @CHILD_NODES (ProcessStep_ID) VALUES ( @CHILD_NODE ) 
			SET @CHILD_NODE = NULL
			SELECT @TEMP_ROW_ID 	= NULL
			SELECT @CHILD_NODE = ProcessStep_ID, @TEMP_ROW_ID = Row_Id FROM @TEMP_CHILD_NODES WHERE Row_Id =  ( SELECT MAX ( Row_Id ) FROM @TEMP_CHILD_NODES )
			DELETE FROM @TEMP_CHILD_NODES WHERE Row_Id = @TEMP_ROW_ID
			WHILE ( @CHILD_NODE IS NOT NULL )
			 BEGIN
				SET @NO_OF_PATHS = @NO_OF_PATHS + 1
				INSERT INTO @PATH_NUMBER_STACK( Path_Number ) VALUES ( @NO_OF_PATHS )
				INSERT INTO @PATHS_TABLE( PATH_NUMBER, PROCESSSTEP_ID ) VALUES ( @NO_OF_PATHS, @CHILD_NODE )
				INSERT INTO @CHILD_NODES (ProcessStep_ID) VALUES ( @CHILD_NODE )	
				SET @CURRENT_PATH_NUMBER = @NO_OF_PATHS
				
				SET @CHILD_NODE	= NULL
				SELECT @TEMP_ROW_ID 	= NULL
				SELECT @CHILD_NODE = ProcessStep_ID, @TEMP_ROW_ID = Row_Id FROM @TEMP_CHILD_NODES WHERE Row_Id =  ( SELECT MAX ( Row_Id ) FROM @TEMP_CHILD_NODES )
				DELETE FROM @TEMP_CHILD_NODES WHERE Row_Id = @TEMP_ROW_ID
			 END
		 END
		ELSE IF ( @TEMP_COUNT = 1 )
		 BEGIN
			SELECT TOP 1 @CHILD_NODE = ProcessStep_ID FROM @TEMP_CHILD_NODES 
			DELETE FROM @TEMP_CHILD_NODES WHERE ProcessStep_ID = @CHILD_NODE
			INSERT INTO @PATHS_TABLE( PATH_NUMBER, PROCESSSTEP_ID ) VALUES ( @CURRENT_PATH_NUMBER, @CHILD_NODE )
			INSERT INTO @CHILD_NODES (ProcessStep_ID) VALUES ( @CHILD_NODE )
		 END
		ELSE IF ( @TEMP_COUNT = 0 )
		 BEGIN
			SET @TEMP_ROW_ID 	= NULL
			SELECT @CURRENT_PATH_NUMBER = Path_Number, @TEMP_ROW_ID = Row_Id FROM @PATH_NUMBER_STACK WHERE Row_Id = ( SELECT Max ( Row_Id ) FROM @PATH_NUMBER_STACK )
			DELETE FROM @PATH_NUMBER_STACK WHERE Row_Id = @TEMP_ROW_ID
			SELECT @CURRENT_PATH_NUMBER = Path_Number FROM @PATH_NUMBER_STACK WHERE Row_Id = ( SELECT Max ( Row_Id ) FROM @PATH_NUMBER_STACK )
		 END
		
		SET @Row_ID = NULL
		SELECT @Row_ID = Row_ID, @CURRENT_NODE = ProcessStep_ID FROM @CHILD_NODES WHERE Row_Id = ( SELECT MAX( Row_Id ) FROM @CHILD_NODES )
		
	 END
	
	--TO UPDATE ALL THE FIELDS LIKE PS_TYPE, Product_Name, product_id..
	DECLARE @Product_Name	VARCHAR(50)
	DECLARE @Product_ID	INT
	DECLARE @PS_Type	VARCHAR(50)
	DECLARE @PS_Type_Name	VARCHAR(50)

	DECLARE @PATHS_TABLE_CURSOR 	CURSOR
	SET @PATHS_TABLE_CURSOR		= CURSOR FOR SELECT Row_Id, PROCESSSTEP_ID FROM @PATHS_TABLE
	SET @Row_ID	= NULL
	SET @TEMP_PROCESSSTEP_ID = NULL
	OPEN @PATHS_TABLE_CURSOR
	FETCH NEXT FROM @PATHS_TABLE_CURSOR INTO @Row_ID, @TEMP_PROCESSSTEP_ID
	WHILE ( @@FETCH_STATUS = 0 )
	 BEGIN
		SELECT @Product_Name = P.Part_Number, @Product_ID = P.Product_ID, @PS_Type = PS.PS_Type, @PS_Type_Name = PS.PS_Type_Name FROM Product P, Process_Step PS WHERE PS.ProcessStep_ID = @TEMP_PROCESSSTEP_ID AND P.ProcessStep_ID = PS.ProcessStep_ID
		UPDATE @PATHS_TABLE SET PS_Type = @PS_Type, PS_Type_Name = @PS_Type_Name, Product_Name = @Product_Name, Product_Id = @Product_ID WHERE Row_Id = @Row_ID
		
		SET @Product_Name	= NULL
		SET @Product_ID		= NULL
		SET @PS_Type		= NULL
		SET @PS_Type_Name	= NULL
		SET @Row_ID		= NULL
		SET @TEMP_PROCESSSTEP_ID= NULL

		FETCH NEXT FROM @PATHS_TABLE_CURSOR INTO @Row_ID, @TEMP_PROCESSSTEP_ID
	 END
	RETURN
	
END